package com.example.demo.test;

import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.example.demo.duishouhuming.entity.TyDuishouhuming;
import com.example.demo.duishouhuming.mapper.TyDuishouhumingMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@SpringBootTest
class DemoApplicationTests2 {
    @Autowired
    private TyDuishouhumingMapper tyDuishouhumingMapper;

    @Test
    void contextLoads2() {
        main2();
        System.out.println("操作完成 ");
    }


    public void main2() {
        String path = "C:\\Users\\Administrator\\Desktop\\24\\报案.xlsx";
        //通过sheet名获取
        ExcelReader reader = ExcelUtil.getReader(FileUtil.file(path), "Sheet1");
        List<TyDuishouhuming> list1 = reader.readAll(TyDuishouhuming.class);
        List<TyDuishouhuming> list3 = formatList(list1);
        excelWriterBean3(list3);
    }

    public List<TyDuishouhuming> formatList(List<TyDuishouhuming> rows1) {
        List<TyDuishouhuming> rows3 = new ArrayList<>();
        for (TyDuishouhuming bean52 : rows1) { //我抽出来的数据（小）
            String 收入 = "";
            String 支出 = "";

            Map<String, Object> map1 = new HashMap<>();
            map1.put("name", bean52.get对手户名());
            Map map2 = tyDuishouhumingMapper.selectSum(map1);

            收入 = map2.get("收入").toString();
            支出 = map2.get("支出").toString();

            TyDuishouhuming tyADto = new TyDuishouhuming();
            tyADto.set对手户名(bean52.get对手户名());
            tyADto.set收入(NumberUtil.roundStr(收入, 2));
            tyADto.set支出(NumberUtil.roundStr(支出, 2));
            rows3.add(tyADto);
        }
        return rows3;
    }

    public void excelWriterBean3(List<TyDuishouhuming> rows) {
        String destFilePath="C:\\Users\\Administrator\\Desktop\\24\\报案-out2.xlsx";
        // 通过工具类创建writer
        ExcelWriter writer = ExcelUtil.getWriter(destFilePath);
        // 合并单元格后的标题行，使用默认标题样式
        // writer.merge(4, "一班成绩单");
        // 一次性写出内容，使用默认样式，强制输出标题
        writer.write(rows, true);
        // 关闭writer，释放内存
        writer.close();
    }


}
